import sqlite3
import threading
import serial
import serial.tools.list_ports
import time
from datetime import datetime
import matplotlib
import matplotlib.pyplot as plt
import numpy as np
import xlsxwriter
from docx import Document

# 异常温度阈值
THRESHOLD = 1


class CheckThread(threading.Thread):
    def __init__(self, ):
        threading.Thread.__init__(self)
        self.running = True

    def run(self):
        conn = sqlite3.connect('Tem.db')
        c = conn.cursor()
        lastone = 0
        while self.running:
            cursor = c.execute("SELECT MAX(id),value,time from TemInfo")
            rv = cursor.fetchall()
            # print(len(rv))
            print(rv[len(rv) - 1][1], '℃  ', rv[len(rv) - 1][2])
            # print(int(rv[len(rv) - 1][2][1])+1)
            if abs(lastone - rv[len(rv) - 1][1]) > THRESHOLD:
                print("\033[31m检测到温度异常！\033[0m")
            lastone = rv[len(rv) - 1][1]
            time.sleep(2)

    def terminate(self):
        self.running = False


class TemSensor(threading.Thread):
    def __init__(self, ):
        threading.Thread.__init__(self)
        self.running = True

    def run(self):
        print('初始化传感器...')
        port_list = list(serial.tools.list_ports.comports())
        if len(port_list) <= 0:
            print("\033[33m找不到串口！\033[0m")
        else:
            port_list_0 = list(port_list[0])

            port_serial = port_list_0[0]

            ser = serial.Serial(port_serial, 115200, timeout=520)

            print("Link...", ser.name)
            print("\033[32m传感器连接成功！\033[0m")
            conn = sqlite3.connect('Tem.db')
            c = conn.cursor()
            data = 0
            while self.running:
                while ser.inWaiting() > 0:
                    data = data * 10 + int(ser.read(1))
                if data:
                    c.execute("INSERT INTO TemInfo(value) VALUES(?)", (data,))
                    conn.commit()
                    data = 0

    def terminate(self):
        self.running = False


class User(object):
    def __init__(self):
        self.UserName = []
        self.PassWord = []
        self.Authority = []
        self.ID = []

    def login(self):
        name = input('请输入您的姓名：')
        pswd = input('请输入您的密码：')
        conn = sqlite3.connect('User.db')
        c = conn.cursor()
        cursor = c.execute("SELECT id,name,password,authority FROM UserInfo WHERE name = ?", (name,))
        rv = cursor.fetchall()
        success = 'no'
        if rv:
            if rv[0][2] == pswd:
                self.ID = rv[0][0]
                self.UserName = rv[0][1]
                self.PassWord = rv[0][2]
                self.Authority = rv[0][3]
                success = 'yes'
            else:
                print("密码错误！")
        else:
            print("用户名错误！")
        conn = sqlite3.connect('Login.db')
        c = conn.cursor()
        c.execute("INSERT INTO LoginInfo(name,password,status) VALUES(?,?,?)", (name, pswd, success))
        conn.commit()

    def check_tem(self):
        if self.Authority != 1:
            return print("\033[31m获取权限失败！\033[0m")
        print('权限校验成功！')
        print('读取数据中...')
        print("\033[33mNotice:Press enter to return\033[0m")
        checkthread = CheckThread()
        checkthread.start()
        while True:
            cmd = input()
            if not cmd:
                checkthread.terminate()
                break

    def exit_sys(self):
        pass

    def show_menu(self):
        if self.Authority == 1:
            print('--------------菜单--------------')
            print('1 温度检测')
            print('2 退出系统')
        elif self.Authority == 2:
            print('--------------菜单--------------')
            print('1 温度分析')
            print('2 退出系统')
        elif self.Authority == 3:
            print('--------------菜单--------------')
            print('1 导出数据')
            print('2 权限管理')
            print('3 退出系统')

    def analyse_tem(self):
        if self.Authority != 2:
            return print("\033[31m获取权限失败！\033[0m")
        conn = sqlite3.connect('Tem.db')
        c = conn.cursor()
        cursor = c.execute("SELECT MAX(id),time from TemInfo")
        rv = cursor.fetchall()
        # max_id = rv[0][0]
        max_time = rv[0][1]
        cursor = c.execute("SELECT MIN(id),time from TemInfo")
        rv = cursor.fetchall()
        # min_id = rv[0][0]
        min_time = rv[0][1]
        print('Max date is: ', max_time)
        print('Min date is: ', min_time)
        while True:
            start_time = input('请输入开始时间：')
            end_time = input('请输入结束时间：')
            cursor = c.execute("SELECT value,time from TemInfo WHERE time>=? AND time<=?", (start_time, end_time))
            rv = cursor.fetchall()
            if rv:
                y = [i[0] for i in rv]
                x = [datetime.strptime(d[1], '%Y-%m-%d %H:%M:%S') for d in rv]
                unusual = 0
                for i in range(len(y) - 1):
                    if y[i + 1] - y[i] > THRESHOLD:
                        unusual += 1
                matplotlib.rcParams['font.family'] = 'STSong'
                plt.plot(x, y)
                plt.ylabel('温度（℃）')
                plt.xlabel('时间')
                plt.gcf().autofmt_xdate()
                avg = np.mean(y)
                plt.axhline(y=avg, color="red", linewidth=0.5)
                plt.savefig('test', dpi=600)
                plt.show()
                print("\033[33m温度均值：%f\033[0m", avg)
                print("\033[33m异常点数：%d\033[0m", unusual)
                break
            else:
                print("\033[31m没有找到匹配的数据！\033[0m")

    def export_tem(self):
        if self.Authority != 3:
            return print("\033[31m获取权限失败！\033[0m")
        conn = sqlite3.connect('Tem.db')
        c = conn.cursor()
        cursor = c.execute("SELECT id,value,time from TemInfo")
        rv = cursor.fetchall()
        print('-------------请选择-------------')
        print('1 导出到word')
        print('2 导出到excel')
        while True:
            cmd = input('选择：')
            if cmd == '1':
                document = Document()
                document.add_paragraph('id\t温度/℃\t日期')
                # print(rv[0][2])
                # print(type(rv[0][2]))
                for i in range(len(rv)):
                    document.add_paragraph(str(rv[i][0]) + '\t' + str(rv[i][1]) + '\t\t' + rv[i][2])
                document.save('TemInfo_export.docx')
                break
            elif cmd == '2':
                workbook = xlsxwriter.Workbook('TemInfo_export.xlsx')
                worksheet = workbook.add_worksheet("工作簿1")
                headings = ['id', '温度/℃', '日期']
                worksheet.write_row('A1', headings)
                for row in range(1, len(rv) + 1):
                    for com in [0, 1, 2]:
                        worksheet.write(row, com, rv[row - 1][com])
                workbook.close()
                break
            else:
                print("\033[33m请重新输入！\033[0m")

    def auth_manage(self):
        if self.Authority != 3:
            return print("\033[31m获取权限失败！\033[0m")
        current_user = self.UserName
        conn = sqlite3.connect('User.db')
        c = conn.cursor()

        while True:
            print('-------------请选择-------------')
            print('1 添加人员')
            print('2 删除人员')
            print('3 修改人员')
            print('4 查询人员')
            print('5 返回')
            index = input('选择：')
            if index == '1':
                while True:
                    print("\033[33mNotice:Press enter to return\033[0m")
                    name = input('姓名：')
                    if name != '':
                        try:
                            user_name_is_num=int(name)
                            print("\033[31m用户名不能为数字！\033[0m")
                        except:
                            cursor = c.execute("SELECT name from UserInfo WHERE name=?", (name,))
                            rv = cursor.fetchall()
                            if rv != []:
                                print("\033[31m用户已存在！\033[0m")
                            else:
                                break
                    else:
                        break
                if name == '':
                    continue
                pswd = input('密码：')
                while True:
                    if pswd == '':
                        print("\033[33m密码禁止为空！\033[0m")
                        pswd = input('密码：')
                    else:
                        break
                print('----请选择权限----')
                print('1 检测人员')
                print('2 业务人员')
                print('3 管理人员')
                while True:
                    au = input()
                    if au == '1':
                        authority = 1
                        break
                    elif au == '2':
                        authority = 2
                        break
                    elif au == '3':
                        authority = 3
                        break
                    else:
                        print("\033[33m请重新输入！\033[0m")
                # cursor = c.execute("SELECT name from UserInfo WHERE name=?", (name,))
                # rv = cursor.fetchall()
                # if rv != []:
                c.execute(
                    "INSERT INTO UserInfo(name, password, authority, modby) VALUES(?, ?, ?, ?)",
                    (name, pswd, authority, current_user))
                conn.commit()
                print("\033[32m添加成功！\033[0m")
                # else:
                # print("\033[31m用户已存在！\033[0m")
            elif index == '2':
                cursor = c.execute("SELECT * from UserInfo")
                rv = cursor.fetchall()
                print('{0:4} {1:16} {2:16} {3:1} {4:19} {5:19} {6:16}'.format('id', 'name', 'password', 'authority',
                                                                              'creat_date', 'modify_date', 'modify_by'))
                for i in range(len(rv)):
                    print('{0:4d} {1:16} {2:16} {3:9d} {4:19} {5:19} {6:16}'.format(rv[i][0], rv[i][1], rv[i][2],
                                                                                    rv[i][3], rv[i][4], str(rv[i][5]),
                                                                                    rv[i][6]))
                while True:
                    print("\033[33mNotice:Press enter to return\033[0m")
                    to_delete = input('请输入要删除用户的姓名或编号：')
                    try:
                        to_delete_id = int(to_delete)
                        if to_delete_id == user.ID:
                            print("\033[31m非法请求！\033[0m")
                        else:
                            cursor = c.execute("SELECT id from UserInfo WHERE id=?", (to_delete_id,))
                            rv = cursor.fetchall()
                            if rv != []:
                                c.execute("DELETE from UserInfo where id = ?", (to_delete_id,))
                                conn.commit()
                                print("\033[32m删除成功！\033[0m")
                                break
                            else:
                                print("\033[31m查无此人！\033[0m")
                                continue

                    except:
                        if to_delete == '':
                            break
                        if to_delete == user.UserName:
                            print("\033[31m非法请求！\033[0m")
                        else:
                            cursor = c.execute("SELECT name from UserInfo WHERE name=?", (to_delete,))
                            rv = cursor.fetchall()
                            if rv != []:
                                c.execute("DELETE from UserInfo where name = ?", (to_delete,))
                                conn.commit()
                                print("\033[32m删除成功！\033[0m")
                                break
                            else:
                                print("\033[31m查无此人！\033[0m")
                                continue

            elif index == '3':
                cursor = c.execute("SELECT * from UserInfo")
                rv = cursor.fetchall()
                print('{0:4} {1:16} {2:16} {3:1} {4:19} {5:19} {6:16}'.format('id', 'name', 'password', 'authority',
                                                                              'creat_date', 'modify_date', 'modify_by'))
                for i in range(len(rv)):
                    print('{0:4d} {1:16} {2:16} {3:9d} {4:19} {5:19} {6:16}'.format(rv[i][0], rv[i][1], rv[i][2],
                                                                                    rv[i][3], rv[i][4], str(rv[i][5]),
                                                                                    rv[i][6]))
                while True:
                    print("\033[33mNotice:Press enter to return\033[0m")
                    to_modify = input('请输入要修改用户的姓名或编号：')
                    if to_modify == '':
                        break
                    try:
                        to_modify_id = int(to_modify)
                        # if to_modify_id == user.ID:
                        # print("\033[31m非法请求！\033[0m")
                        # else:
                        cursor = c.execute("SELECT id from UserInfo WHERE id=?", (to_modify_id,))
                        rv = cursor.fetchall()
                        if rv == []:
                            print("\033[31m查无此人！\033[0m")
                            continue
                        print('------选项------')
                        print('1 修改密码')
                        print('2 修改权限')
                        print('3 取消')
                        while True:
                            option = input('选择：')
                            if option == '1':
                                break
                            elif option == '2':
                                if to_modify_id == user.ID:
                                    print("\033[31m非法请求！\033[0m")
                                else:
                                    break
                            elif option == '3':
                                break
                            else:
                                print("\033[33m请重新输入！\033[0m")
                        if option == '1':
                            new_pass = input('新密码：')
                            while new_pass == '':
                                print("\033[33m密码不能为空！\033[0m")
                                new_pass = input('新密码：')
                            c.execute(
                                "UPDATE UserInfo set password = ?, moddate = CURRENT_TIMESTAMP, modby = ? where id = ? ",
                                (new_pass, current_user, to_modify_id))
                            print("\033[32m修改成功！\033[0m")
                        elif option == '2':
                            print('----请选择权限----')
                            print('1 检测人员')
                            print('2 业务人员')
                            print('3 管理人员')
                            while True:
                                au = input()
                                if au == '1':
                                    new_auth = 1
                                    break
                                elif au == '2':
                                    new_auth = 2
                                    break
                                elif au == '3':
                                    new_auth = 3
                                    break
                                else:
                                    print("\033[33m请重新输入！\033[0m")
                            c.execute(
                                "UPDATE UserInfo set authority = ?, moddate = CURRENT_TIMESTAMP, modby = ? where id = ? ",
                                (new_auth, current_user, to_modify_id))
                            print("\033[32m修改成功！\033[0m")
                        elif option == '3':
                            continue

                        conn.commit()
                        break
                    except:
                        # if to_modify == user.UserName:
                        #     print("\033[31m非法请求！\033[0m")
                        # else:
                        cursor = c.execute("SELECT name from UserInfo WHERE name=?", (to_modify,))
                        rv = cursor.fetchall()
                        if rv == []:
                            print("\033[31m查无此人！\033[0m")
                            continue
                        print('------选项------')
                        print('1 修改密码')
                        print('2 修改权限')
                        print('3 取消')
                        while True:
                            option = input('选择：')
                            if option == '1':
                                break
                            elif option == '2':
                                if to_modify == user.UserName:
                                    print("\033[31m非法请求！\033[0m")
                                else:
                                    break
                            elif option == '3':
                                break
                            else:
                                print("\033[33m请重新输入！\033[0m")
                        if option == '1':
                            new_pass = input('新密码：')
                            while new_pass == '':
                                print("\033[33m密码不能为空！\033[0m")
                                new_pass = input('新密码：')
                            c.execute(
                                "UPDATE UserInfo set password = ?, moddate = CURRENT_TIMESTAMP, modby = ? where name = ? ",
                                (new_pass, current_user, to_modify))
                            print("\033[32m修改成功！\033[0m")
                        elif option == '2':
                            print('----请选择权限----')
                            print('1 检测人员')
                            print('2 业务人员')
                            print('3 管理人员')
                            while True:
                                au = input()
                                if au == '1':
                                    new_auth = 1
                                    break
                                elif au == '2':
                                    new_auth = 2
                                    break
                                elif au == '3':
                                    new_auth = 3
                                    break
                                else:
                                    print("\033[33m请重新输入！\033[0m")
                            c.execute(
                                "UPDATE UserInfo set authority = ?, moddate = CURRENT_TIMESTAMP, modby = ? where name = ? ",
                                (new_auth, current_user, to_modify))
                            print("\033[32m修改成功！\033[0m")
                        elif option == '3':
                            continue
                        conn.commit()
                        break
            elif index == '4':
                cursor = c.execute("SELECT * from UserInfo")
                rv = cursor.fetchall()
                print('{0:4} {1:16} {2:16} {3:1} {4:19} {5:19} {6:16}'.format('id', 'name', 'password', 'authority',
                                                                              'creat_date', 'modify_date', 'modify_by'))
                for i in range(len(rv)):
                    print('{0:4d} {1:16} {2:16} {3:9d} {4:19} {5:19} {6:16}'.format(rv[i][0], rv[i][1], rv[i][2],
                                                                                    rv[i][3], rv[i][4], str(rv[i][5]),
                                                                                    rv[i][6]))
                while True:
                    print("\033[33mNotice:Press enter to return\033[0m")
                    retrieve = input('请输入要查询用户的姓名或编号：')
                    if retrieve == '':
                        break
                    try:
                        retrieve_id = int(retrieve)
                        cursor = c.execute("SELECT * from UserInfo WHERE id=?", (retrieve_id,))
                        rv = cursor.fetchall()
                        if rv == []:
                            print("\033[31m查无此人！\033[0m")
                            continue
                        else:
                            print('{0:4} {1:16} {2:16} {3:1} {4:19} {5:19} {6:16}'.format('id', 'name', 'password',
                                                                                          'authority',
                                                                                          'creat_date', 'modify_date',
                                                                                          'modify_by'))
                            for i in range(len(rv)):
                                print('{0:4d} {1:16} {2:16} {3:9d} {4:19} {5:19} {6:16}'.format(rv[i][0], rv[i][1],
                                                                                                rv[i][2],
                                                                                                rv[i][3], rv[i][4],
                                                                                                str(rv[i][5]),
                                                                                                rv[i][6]))
                            print("\033[32m查询成功！\033[0m")
                    except:
                        cursor = c.execute("SELECT * from UserInfo WHERE name=?", (retrieve,))
                        rv = cursor.fetchall()
                        if rv == []:
                            print("\033[31m查无此人！\033[0m")
                            continue
                        else:
                            print('{0:4} {1:16} {2:16} {3:1} {4:19} {5:19} {6:16}'.format('id', 'name', 'password',
                                                                                          'authority',
                                                                                          'creat_date', 'modify_date',
                                                                                          'modify_by'))
                            for i in range(len(rv)):
                                print('{0:4d} {1:16} {2:16} {3:9d} {4:19} {5:19} {6:16}'.format(rv[i][0], rv[i][1],
                                                                                                rv[i][2],
                                                                                                rv[i][3], rv[i][4],
                                                                                                str(rv[i][5]),
                                                                                                rv[i][6]))
                            print("\033[32m查询成功！\033[0m")
            elif index == '5':
                break
            else:
                print("\033[33m请重新输入！\033[0m")

    # used for debug
    @staticmethod  # 静态方法无需实例化
    def crtusrlib():
        conn = sqlite3.connect('User.db')
        c = conn.cursor()
        c.execute("DROP TABLE IF EXISTS UserInfo")
        c.execute('''CREATE TABLE UserInfo
                        (id     INTEGER PRIMARY KEY    NOT NULL,
                        name            char(16)       NOT NULL,
                        password        char(16)       NOT NULL,
                        authority       INT            NOT NULL,
                        crtdate         timestamp default(CURRENT_TIMESTAMP),
                        moddate         timestamp,
                        modby           char(16)       NOT NULL);''')
        conn.commit()
        conn.close()

    @staticmethod  # 静态方法无需实例化
    def crtloginf():
        conn = sqlite3.connect('Login.db')
        c = conn.cursor()
        c.execute("DROP TABLE IF EXISTS LoginInfo")
        c.execute('''CREATE TABLE LoginInfo
                           (id INTEGER PRIMARY KEY            NOT NULL,
                           name            char(16)       NOT NULL,
                           password        char(16)       NOT NULL,
                           logdate         timestamp default(CURRENT_TIMESTAMP),
                           status          char(4)       NOT NULL);''')
        conn.commit()
        conn.close()

    @staticmethod  # 静态方法无需实例化
    def crttemlib():
        conn = sqlite3.connect('Tem.db')
        c = conn.cursor()
        c.execute("DROP TABLE IF EXISTS TemInfo")
        c.execute('''CREATE TABLE TemInfo
                            (id INTEGER PRIMARY KEY            NOT NULL,
                            value           DOUBLE         NOT NULL,
                            time            timestamp default(CURRENT_TIMESTAMP));''')
        conn.commit()
        conn.close()

    @staticmethod  # 静态方法无需实例化
    def addtestuser():
        conn = sqlite3.connect('User.db')
        c = conn.cursor()
        c.execute("INSERT INTO UserInfo(name, password, authority, modby) VALUES('organize', '0528', 3, 'Yaadon')")
        c.execute("INSERT INTO UserInfo(name, password, authority, modby) VALUES('analyse', '0528', 2, 'Yaadon')")
        c.execute("INSERT INTO UserInfo(name, password, authority, modby) VALUES('check', '0528', 1, 'Yaadon')")
        conn.commit()
        conn.close()

    @staticmethod  # 静态方法无需实例化
    def addtesttem():
        conn = sqlite3.connect('Tem.db')
        c = conn.cursor()
        c.execute("INSERT INTO TemInfo(value) VALUES(25.6)")
        c.execute("INSERT INTO TemInfo(value) VALUES(25.7)")
        c.execute("INSERT INTO TemInfo( value) VALUES(26.0)")
        conn.commit()
        conn.close()

    # end of debug


if __name__ == '__main__':
    # User.crtusrlib()
    # User.crttemlib()
    # User.crtloginf()
    # User.addtesttem()
    # User.addtestuser()
    print('=========基于STM温度检测管理系统========')
    sensor = TemSensor()
    sensor.start()
    user = User()
    time.sleep(0.1)
    while not user.Authority:
        user.login()
    print("登录成功！")
    my_id = []
    if user.Authority == 1:
        my_id = '检测人员'
        print('您的身份是：', my_id)
        while True:
            cmd = 0
            user.show_menu()
            while True:
                cmd = input()
                if cmd == '1' or cmd == '2':
                    break
                else:
                    print("\033[33m请重新输入！\033[0m")
            if cmd == '1':
                user.check_tem()
            else:
                sensor.terminate()
                break
    elif user.Authority == 2:
        my_id = '业务人员'
        print('您的身份是：', my_id)
        while True:
            cmd = 0
            user.show_menu()
            while True:
                cmd = input()
                if cmd == '1' or cmd == '2':
                    break
                else:
                    print("\033[33m请重新输入！\033[0m")
            if cmd == '1':
                user.analyse_tem()
            else:
                sensor.terminate()
                break
    elif user.Authority == 3:
        my_id = '管理人员'
        print('您的身份是：', my_id)
        while True:
            cmd = 0
            user.show_menu()
            while True:
                cmd = input()
                if cmd == '1' or cmd == '2' or cmd == '3':
                    break
                else:
                    print("\033[33m请重新输入！\033[0m")
            if cmd == '1':
                user.export_tem()
            elif cmd == '2':
                user.auth_manage()
            else:
                sensor.terminate()
                break
    print('安全退出！')
